package com.ced.base.dao.impl;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.ced.base.dao.IBaseDao;
import com.ced.base.exception.BaseException;
import com.ced.base.exception.ExceptionRescoure;
import com.ced.base.utils.RollPage;

public class BaseDaoImpl extends HibernateDaoSupport implements IBaseDao {

	protected final Log log = LogFactory.getLog(getClass());
	/**
	 * 获取Connection
	 */
	public Connection getConnection()  throws BaseException {
		 Connection con =null; 
		try{ 
			Session curSeesion =null; 
            curSeesion =getHibernateTemplate().getSessionFactory().getCurrentSession();
            con = curSeesion.connection();
         } catch (Exception ex){
			log.error(ex.getMessage());
        	throw new BaseException(ExceptionRescoure.LOGIN_EXCEPTION);
          }
            return con;
	}

	/**
	 * 获取HibernateSession
	 */
	public Session getTrSession() throws BaseException {
		Session session = null;
		try {
			session = getSession();
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.LOGIN_EXCEPTION);
		}
		return session;
	}
	
	/**
	 * 保存数据
	 */
	public void saveObject(Object o) throws BaseException {
		try {
			Session session = getSession();
			//session.merge(o);
			getHibernateTemplate().save(o);
			session.flush();
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_SAVE_EXCEPTION);
		}
	}
	/**
	 * 更新数据
	 */
	public void updateObject(Object o) throws BaseException{
		try {
			Session session = getSession();
			getHibernateTemplate().update(o);
			session.flush();
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw	new BaseException(ExceptionRescoure.HIBERNATE_UPDATE_EXCEPTION);
		}
	}

	/**
	 * 更新数据
	 */
	public void updateObjectByHql(String hql) throws BaseException{
		try {
			super.getSession().createQuery(hql).executeUpdate();
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw	new BaseException(ExceptionRescoure.HIBERNATE_UPDATE_EXCEPTION);
		}
	}
	/**
	 * 修改或是更新数据
	 */
	public void saveOrUpdateObject(Object o)throws BaseException {
		try {
			getHibernateTemplate().saveOrUpdate(o);
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_SAVE_EXCEPTION);
		}
	}
	
	/**
	 * 删除数据
	 */
	public void removeObject( Object o) throws BaseException {
		try {
			getHibernateTemplate().delete( o );
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw	new BaseException(ExceptionRescoure.HIBERNATE_DELETE_EXCEPTION);
		}
	}
	
	/**
	 * 删除数据
	 */
	public void removeObject(Class clazz, Serializable id) throws BaseException {
		try {
			getHibernateTemplate().delete(getObject(clazz, id));
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw	new BaseException(ExceptionRescoure.HIBERNATE_DELETE_EXCEPTION);
		}
	}


	/**
	 * 批量删除数据
	 */
	public void removeBatchObject(Class clazz, Serializable[] id) throws BaseException {
		Session session = getSession();
		try {
			for (int i = 0; i < id.length; i++) {
				session.delete(getObject(clazz, Long.valueOf(id[i].toString())));
				// 当记录数达到20条时，将数据写入数据库，并清理内存
				if (i != 0 && i + 1 % 20 == 0) {
					session.flush();
					session.clear();
				}
			}

			session.flush();
			session.clear();
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw	new BaseException(
					ExceptionRescoure.HIBERNATE_BATCH_DELETE_EXCEPTION);
		}
	}
	/**
	 * 截取sql语句查询总数
	 * @param hql
	 * @return
	 * @throws BaseException
	 */
	 public int getConut(String hql) throws BaseException{
		 int count = 0;
			try {
				//此处是截取sql进行拼接查询 第一个from  最后一个order 之前的sql语句
			   if(hql.indexOf("order")!=-1)
			       hql=hql.substring(hql.indexOf("from"),hql.lastIndexOf("order"));
			   else
				   hql=hql.substring(hql.indexOf("from"));   
		       hql="select count(*) "+hql;
		       Query q=getSession().createQuery(hql);
		       List list = q.list();
		       String li=list.get(0).toString();
		       count=Integer.parseInt(li);
			} catch (Exception ex) {
				log.error(ex.getMessage());
				throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
			}
			
			return count;
	}
	/**
	 * 数据查询（根据ID查询）
	 */
	public Object getObject(Class clazz, Serializable id) throws BaseException {
		try {
			Object o = getHibernateTemplate().get(clazz, id);
			if (o == null) {
				throw new BaseException(ExceptionRescoure.HIBERNATE_NOT_FIND);
			}
			return o;
		} catch (Exception ex) {
			log.error(ex.getMessage());
			ex.printStackTrace() ;
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}

	}

	/**
	 * 数据查询，查询所有数据
	 */
	public List getObjects(Class clazz)throws BaseException {
		try {
//			((Integer) getHibernateTemplate().iterate("").next()).intValue() ;
			return getHibernateTemplate().loadAll(clazz);
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}
	}

	/**
	 * 统计数据条数
	 * 
	 * @param hql
	 * @return
	 * @throws BaseException
	 */
	public int count(String hql) throws BaseException {
		int count = 0;
		try {
			Iterator iter = getHibernateTemplate().iterate( hql );
			if( iter.hasNext() ) {
				count = ( (Long) iter.next() ).intValue();
			}
//			count = ((Integer) .next()).intValue() ;
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}
		
		return count;
		
	}
	/**
	 * 计算之和 sum 最小值min 最大值max
	 * 
	 * @param hql
	 * @return
	 * @throws BaseException
	 */
	public Object sumOrMinOrMax(String hql,Class clazz) throws BaseException {
		Object sum=0;
		try {
			Iterator iter = getHibernateTemplate().iterate( hql );
			if( iter.hasNext() ) {
				Object t=iter.next();
				if(clazz.equals(Double.class))
					if(t!=null)  sum = (Double) t ; else sum=0.0;
				if(clazz.equals(Integer.class))
					if(t!=null)  sum = (Integer) t ;
			}
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}
		
		return sum;
		
	}
	/**
	 * 封装HQL查询
	 * @param rollPage 翻页
	 * @param hql 
	 * @return
	 */
	public List getObjects(RollPage rollPage, String hql) throws BaseException {
		try{
			Query crt = getSession().createQuery(hql);
			int count=getConut(hql);
			// 翻页设置
			settingRollPage(count,rollPage, crt);
			List list = crt.list();
			return list != null && list.size() > 0 ? list : new ArrayList() ;
		} catch (Exception ex) {
			log.error(ex.getMessage());
			ex.printStackTrace() ;
			
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}
	}

	/**
	 * HQL查询 
	 * @param hql 
	 * @return
	 */
	public List getObjects(String hql)  throws BaseException {
		try{
			Query crt = getSession().createQuery(hql);
			List list = crt.list();
			return list != null && list.size() > 0 ? list : new ArrayList();
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}
	}
	public List getObjects(RollPage rollPage, List list )
	{
		if (rollPage != null) {
			
			// 设置记录总数
			rollPage.setRowCount(list.size());
			// 初始化
			rollPage.init();
			// 设置查找记录的起始位置
			rollPage.getPageFirst();
			// 设置查找记录的最大条数
			rollPage.getPagePer();
			
		}
		return list != null && list.size() > 0 ? list : new ArrayList();
	}
	
	/**
	 * 移除Session中的对象
	 * 
	 * @param obj
	 *            对象obj
	 * @throws BaseException
	 */
	public void removeSessionObject(Object obj) throws BaseException {
		if (getSession().contains(obj)) {
			getSession().evict(obj);
		}
	}

	/**
	 * 
	 * 用Query实现翻页(Hql)
	 * 
	 * @param rollPage
	 *            翻页类
	 * @param Query
	 * 
	 */
	protected void settingRollPage(int count,RollPage rollPage, Query crt) {
		// 翻页设置
		if (rollPage != null) {
			// 设置记录总数
			rollPage.setRowCount(count);
			// 初始化
			rollPage.init();
			// 设置查找记录的起始位置
			crt.setFirstResult(rollPage.getPageFirst());
			// 设置查找记录的最大条数
			crt.setMaxResults(rollPage.getPagePer());
		}
	}

	/**
	 * 
	 * 用java.sql实现翻页(sql)
	 * 
	 * @param rollPage
	 *            翻页类
	 * @param sql
	 *            查询语句
	 * 
	 */
	protected ResultSet settingRollPage(RollPage rollPage, String sql)
			throws BaseException {

		try {
			// 获取连接
			Connection conn = getSession().connection();
			ResultSet res=null;
			Statement stat = conn.createStatement();
			
			if(rollPage!=null){
			String countSql="";
			countSql="select count(*) from ("+sql+") count_table";
			   
			res=stat.executeQuery(countSql); 
			int count=0;
			if(res.next()){
				count=res.getInt(1);
			}
			// 设置总页数
			rollPage.setRowCount(count);
			rollPage.init();
			
			sql=getRollPageString(rollPage,sql);
			
			}
			stat = conn
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			res = stat.executeQuery(sql);
			
			return res;
		} catch (Exception e) {
			BaseException ex = new BaseException();
			ex.setMessageKey("settingRollPage分页查询错误(SQL)！");
			throw ex;
		}
	}

	// 进行SQL分页设置
	private String getRollPageString(RollPage rollPage, String sql) {
		StringBuffer pagingSelect = new StringBuffer();

		pagingSelect
				.append("select * from ( select row_.*, rownum rownum_ from ( ");
		pagingSelect.append(sql);
		pagingSelect.append(" ) row_ where rownum <= ");
		pagingSelect.append(rollPage.getPageFirst()+rollPage.getPagePer());
		pagingSelect.append(") where rownum_ > ");
		pagingSelect.append(rollPage.getPageFirst());

		return pagingSelect.toString();
	}

	/**
	 * 执行返回泛型集合的SQL语句
	 * 
	 * @param cls
	 *            泛型类型
	 * @param sql
	 *            查询SQL语句
	 * @return 泛型集合
	 * @throws BaseException
	 */
	public <T> List<T> getObjectsList(Class<T> cls, String sql)
			throws BaseException {
		List<T> list = new ArrayList<T>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				T obj = executeResultSet(cls, rs);
				list.add(obj);
			}
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		} finally {
			close(con, ps, rs);
		}
		return list;
	}
	/**
	 * 执行返回泛型集合的SQL语句
	 * 
	 * @param cls
	 *            泛型类型
	 * @param sql
	 *            查询SQL语句
	 * @return 泛型集合
	 * @throws BaseException
	 */
	public <T> List<T> getObjectsListRollPage(Class<T> cls,RollPage rollPage, String sql)
			throws BaseException {
		List<T> list = new ArrayList<T>();
		ResultSet rs = null;
		try {

			//System.out.println(new Date().getTime());
			rs = settingRollPage(rollPage,sql);
			while (rs.next()) {
				T obj = executeResultSet(cls, rs);
				list.add(obj);
			}

			//System.out.println(new Date().getTime());
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}
		return list;
	}
	/**
	 * 将一条记录转成一个对象
	 * 
	 * @param cls
	 *            泛型类型
	 * @param rs
	 *            ResultSet对象
	 * @return 泛型类型对象
	 * @throws BaseException
	 */
	private  <T> T executeResultSet(Class<T> cls, ResultSet rs)
			throws BaseException {
		T obj;ResultSetMetaData rsm;
		String fieldInfo="";
		try {
			obj = cls.newInstance();
			rsm = rs.getMetaData();
			int columnCount = rsm.getColumnCount();
			// Field[] fields = cls.getFields();
			Field[] fields = cls.getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				String fieldName = field.getName();
				for (int j = 1; j <= columnCount; j++) {
					String columnName = rsm.getColumnName(j).replace("_", "");
					int scale= rsm.getScale(j);
					if (fieldName.equalsIgnoreCase(columnName)) {
						Object value = rs.getObject(j);
						field.setAccessible(true);	
						if(value!=null){
							fieldInfo=field.toString();
						  if(fieldInfo.contains("java.lang.Long")){
							  field.set(obj, Long.parseLong(value.toString()));
						  }else if(fieldInfo.contains("java.lang.Double")){
							  field.set(obj, Double.parseDouble(value.toString()));
						  }else if(fieldInfo.contains("java.lang.Integer")){
							  field.set(obj, Integer.parseInt(value.toString()));
						  }else if(fieldInfo.contains("java.math.BigDecimal")){
							  field.set(obj, new BigDecimal(value.toString()));
						  }else{
							  field.set(obj, value); 
						  }
                        }
						else{
						    field.set(obj, null);
                        }
						break;
					}
				}
			}
		} catch (Exception ex) {
			log.error(ex.getMessage());
			ex.printStackTrace();
			throw new BaseException(ExceptionRescoure.HIBERNATE_OBJECT_EXCEPTION);
		}
		return obj;
	}

	/**
	 * 关闭JDBC对象，释放资源。
	 * 
	 * @param con
	 *            连接对象
	 * @param ps
	 *            命令对象
	 * @param rs
	 *            结果集对象
	 * @throws SQLException
	 */
	private  void close(Connection con, Statement ps, ResultSet rs)
			throws BaseException {
		try {
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (con != null) {
			con.close();
			con = null;
		}
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_CLOSE_EXCEPTION);
		}
	}
	/**
	 * 自定义执行sql
	 * @param sql 
	 * @return
	 */
	public int updateJdbcSql(String sql) throws BaseException {
		int i=0;
		Connection connection=null;
		try {
			connection =getConnection();
			PreparedStatement pst =  connection.prepareStatement(sql.toString());
		    i=pst.executeUpdate();   	    
		    pst.close();
	    } catch (Exception e) {		
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}	
			throw new BaseException(ExceptionRescoure.JDBC_UPDATE_EXCEPTION);
		}finally{
			close(connection, null,null);
		}
		return i;
	}
	/**
	 * 获得Sequence的下一个值
	 * @author luguanglei 2016-08-25
	 * @return
	 * @throws BaseException 
	 */
	public String getSequenceNextValue(String seqName) {  
	    String seqValue = "";
		try {  
	        String sql = "select "+seqName+".nextval nextvalue from dual";  
	        Integer maxId = (Integer)(super.getSession().createSQLQuery(sql).addScalar("nextvalue", Hibernate.INTEGER) ).uniqueResult();  
	        seqValue = maxId+"";
	    } catch (Exception e) {  
	        e.printStackTrace();  
	    }  
	    return seqValue;  
	} 
	/**
	 * 未经封装的SQL查询
	 * 
	 * @param rollPage
	 *            翻页
	 * @param sql
	 *            SQL语句
	 * @param column
	 *            列名
	 * @return List 结果集
	 * @throws BaseException 
	 */
	public List getObjects(String sql, int columnCount) throws BaseException {
		List list = new ArrayList();
		List tempList = null;
		try {
			ResultSet rs = settingRollPage(null, sql);
			while (rs.next()) {
				tempList = new ArrayList();
				for (int i = 1; i <=columnCount; i++) {
					tempList.add(rs.getString(i));
				}
				    list.add(tempList);
			}
			
			return list.size() > 0 ? list : new ArrayList();
			
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		}

	}
	/**
	 * 计算之和 sum 最小值min 最大值max
	 * 
	 * @param sql
	 * @return
	 * @throws BaseException
	 */
	public Object sumOrMinOrMaxJdbcSql(String sql,Class clazz) throws BaseException {
		Object sum=0;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				Object t=rs.getObject(1);
				if(clazz.equals(Double.class))
					    if(t!=null) sum = (Double) t ; else sum=0.0;
					if(clazz.equals(Integer.class))
						if(t!=null) sum = (Integer) rs.getObject(1) ;
			}
		} catch (Exception ex) {
			log.error(ex.getMessage());
			throw new BaseException(ExceptionRescoure.HIBERNATE_QUERY_EXCEPTION);
		} finally {
			close(con, ps, rs);
		}
		
		return sum;
		
	}
}
